<html>
 <head>
  <meta charset="UTF-8">
 </head>
 <body>
  <h1 data-lake-id="i83Aa" id="i83Aa"><span data-lake-id="ud06454dd" id="ud06454dd">典型回答</span></h1>
  <p data-lake-id="uecf2094c" id="uecf2094c"><br></p>
  <p data-lake-id="u16a1d77c" id="u16a1d77c"><span data-lake-id="u5303bc50" id="u5303bc50">MySQL的索引失效是一个比较常见的问题，这种情况一般会在慢SQL发生时需要考虑，考虑是否存在索引失效的问题。</span></p>
  <p data-lake-id="ufdfb9ca9" id="ufdfb9ca9"><span data-lake-id="ue659ce38" id="ue659ce38">​</span><br></p>
  <p data-lake-id="u8cd36fd7" id="u8cd36fd7"><span data-lake-id="u853c6713" id="u853c6713">在排查索引失效的时候，第一步一定是找到要分析的SQL语句，然后通过explain查看他的执行计划。主要关注type、key和extra这几个字段。</span></p>
  <p data-lake-id="u2b735d30" id="u2b735d30"><span data-lake-id="u819180bd" id="u819180bd">​</span><br></p>
  <p data-lake-id="u2d7513f3" id="u2d7513f3"><br></p>
  <p data-lake-id="u07d37830" id="u07d37830"><strong><span data-lake-id="u8d31ead9" id="u8d31ead9">我们需要通过key+type+extra来判断一条SQL语句是否用到了索引</span></strong><span data-lake-id="u4afcc0b8" id="u4afcc0b8">。如果有用到索引，那么是走了覆盖索引呢？还是索引下推呢？还是扫描了整颗索引树呢？或者是用到了索引跳跃扫描等等。</span></p>
  <p data-lake-id="u5bd69815" id="u5bd69815"><span data-lake-id="u6de81b9f" id="u6de81b9f">​</span><br></p>
  <p data-lake-id="u15bb82b4" id="u15bb82b4"><span data-lake-id="ubca54e25" id="ubca54e25">一般来说，比较理想的走索引的话，应该是以下几种情况：</span></p>
  <p data-lake-id="uebfec46a" id="uebfec46a"><span data-lake-id="u2fa9237c" id="u2fa9237c">​</span><br></p>
  <blockquote data-lake-id="ud0f92e0d" id="ud0f92e0d">
   <p data-lake-id="ue8a673d6" id="ue8a673d6"><span data-lake-id="ufcacee2b" id="ufcacee2b">首先，key一定要有值，不能是NULL</span></p>
   <p data-lake-id="ubd1854f0" id="ubd1854f0"><span data-lake-id="u3d99ab2c" id="u3d99ab2c">其次，type应该是ref、eq_ref、range、const等这几个</span></p>
   <p data-lake-id="u14f2e661" id="u14f2e661"><span data-lake-id="ud7c39db4" id="ud7c39db4">还有，extra的话，如果是NULL，或者using index，using index condition都是可以的</span></p>
  </blockquote>
  <p data-lake-id="ua6127d32" id="ua6127d32"><br></p>
  <p data-lake-id="ubc15d9ca" id="ubc15d9ca"><strong><span data-lake-id="ud2582263" id="ud2582263">如果通过执行计划之后，发现一条SQL没有走索引，比如 type = ALL， key = NULL , extra = Using where</span></strong></p>
  <p data-lake-id="u5a7a4c02" id="u5a7a4c02"><span data-lake-id="uc3b4e5a2" id="uc3b4e5a2">​</span><br></p>
  <p data-lake-id="u0182c523" id="u0182c523"><span data-lake-id="u21a30096" id="u21a30096">那么就要进一步分析没有走索引的原因了。我们需要知道的是，</span><strong><span data-lake-id="u4de4b37e" id="u4de4b37e">到底要不要走索引，走哪个索引，是MySQL的优化器决定的，他会根据预估的成本来做一个决定。</span></strong></p>
  <p data-lake-id="u1ecee2a8" id="u1ecee2a8"><br></p>
  <p data-lake-id="u3a1b0ded" id="u3a1b0ded"><span data-lake-id="uf026c17a" id="uf026c17a">那么，有以下这么几种情况可能会导致没走索引：</span></p>
  <p data-lake-id="ua82b65ad" id="ua82b65ad"><span data-lake-id="uc8a85df4" id="uc8a85df4">​</span><br></p>
  <p data-lake-id="u21ea012a" id="u21ea012a"><span data-lake-id="u051b4dc8" id="u051b4dc8">1、</span><strong><span data-lake-id="u7c587fcd" id="u7c587fcd">没有正确创建索引</span></strong><span data-lake-id="ufbb1d26f" id="ufbb1d26f">：当查询语句中的where条件中的字段，没有创建索引，或者不符合最左前缀匹配的话，就是没有正确的创建索引。</span></p>
  <p data-lake-id="u9b159cd1" id="u9b159cd1"><span data-lake-id="u05add5dd" id="u05add5dd">2、</span><strong><span data-lake-id="uf06f30c2" id="uf06f30c2">索引区分度不高</span></strong><span data-lake-id="u6d350317" id="u6d350317">：如果索引的区分度不够高，那么可能会不走索引，因为这种情况下走索引的效率并不高。</span></p>
  <p data-lake-id="u9aafa246" id="u9aafa246"><span data-lake-id="u38c6601a" id="u38c6601a">3、</span><strong><span data-lake-id="u2abc22ff" id="u2abc22ff">表太小</span></strong><span data-lake-id="u8a9b5a62" id="u8a9b5a62">：当表中的数据很小，优化器认为扫全表的成本也不高的时候，也可能不走索引</span></p>
  <p data-lake-id="u688c0f55" id="u688c0f55"><span data-lake-id="u043c7cb2" id="u043c7cb2">4、</span><strong><span data-lake-id="ud201c042" id="ud201c042">查询语句中，索引字段因为用到了函数、类型不一致等导致了索引失效</span></strong></p>
  <p data-lake-id="u3dbea854" id="u3dbea854"><span data-lake-id="u3f9e0688" id="u3f9e0688">​</span><br></p>
  <p data-lake-id="u3ba1885f" id="u3ba1885f"><span data-lake-id="u0596fc37" id="u0596fc37">这时候我们就需要从头开始逐一分析：</span></p>
  <p data-lake-id="u5e42971a" id="u5e42971a"><span data-lake-id="u36de67d6" id="u36de67d6">​</span><br></p>
  <p data-lake-id="ucef71231" id="ucef71231"><span data-lake-id="u521f0f98" id="u521f0f98">1、如果没有正确创建索引，那么就根据SQL语句，创建合适的索引。如果没有遵守最左前缀那么就调整一下索引或者修改SQL语句</span></p>
  <p data-lake-id="u930a1c4b" id="u930a1c4b"><span data-lake-id="ud14b3981" id="ud14b3981">2、索引区分度不高的话，那么就考虑换一个索引字段。</span></p>
  <p data-lake-id="u46756146" id="u46756146"><span data-lake-id="ub6d6483d" id="ub6d6483d">3、表太小这种情况确实也没啥优化的必要了，用不用索引可能影响不大的</span></p>
  <p data-lake-id="ud8f4ac7b" id="ud8f4ac7b"><span data-lake-id="u2c60a383" id="u2c60a383">4、排查具体的失效原因，然后针对性的调整SQL语句就行了。</span></p>
  <p data-lake-id="ud2a77bf4" id="ud2a77bf4"><span data-lake-id="ue54dbbbb" id="ue54dbbbb">​</span><br></p>
  <p data-lake-id="u1a8e5a35" id="u1a8e5a35"><span data-lake-id="u25f50970" id="u25f50970">​</span><br></p>
  <h1 data-lake-id="DuQjk" id="DuQjk"><span data-lake-id="u1093d971" id="u1093d971">扩展知识</span></h1>
  <p data-lake-id="u6fe80075" id="u6fe80075"><br></p>
  <h2 data-lake-id="FjZSV" id="FjZSV"><span data-lake-id="u68eb1f31" id="u68eb1f31">可能导致索引失效的情况</span></h2>
  <p data-lake-id="u097796b0" id="u097796b0"><br></p>
  <p data-lake-id="u2e6de3bb" id="u2e6de3bb"><span data-lake-id="u0d97f110" id="u0d97f110">假设我们有一张表（以下SQL实验基于Mysql 5.7）：</span></p>
  <pre lang="java"><code>
CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `age` (`age`),
  KEY `create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into mytable(id,name,age,create_time) values (1,"hollis",20,now());
insert into mytable(id,name,age,create_time) values (2,"hollis1",21,now());
insert into mytable(id,name,age,create_time) values (3,"hollis2",22,now());
insert into mytable(id,name,age,create_time) values (4,"hollis3",20,now());
insert into mytable(id,name,age,create_time) values (5,"hollis4",14,now());
insert into mytable(id,name,age,create_time) values (6,"hollis5",43,now());
insert into mytable(id,name,age,create_time) values (7,"hollis6",32,now());
insert into mytable(id,name,age,create_time) values (8,"hollis7",12,now());
insert into mytable(id,name,age,create_time) values (9,"hollis8",1,now());
insert into mytable(id,name,age,create_time) values (10,"hollis9",43,now());

</code></pre>
  <p data-lake-id="u72e4065b" id="u72e4065b"><br></p>
  <h3 data-lake-id="Utt15" id="Utt15"><span data-lake-id="u74521ec4" id="u74521ec4">索引列参与计算</span></h3>
  <p data-lake-id="u73fd951c" id="u73fd951c"><br></p>
  <pre lang="java"><code>
select * from mytable where age = 12;

+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+                                                                
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |                                                                
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+                                                                
|  1 | SIMPLE      | mytable | NULL       | ref  | age           | age  | 5       | const |    1 |   100.00 | NULL  |                                                                
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+                                                                
</code></pre>
  <p data-lake-id="ud1582179" id="ud1582179"><br></p>
  <p data-lake-id="u83ad7feb" id="u83ad7feb"><span data-lake-id="u0268ef76" id="u0268ef76">以上SQL是可以走索引的，但是如果我们在字段中增加计算的话，就会索引失效：</span></p>
  <p data-lake-id="ueac04a1a" id="ueac04a1a"><br></p>
  <pre lang="java"><code>
select * from mytable where age +1 = 12;

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
|  1 | SIMPLE      | mytable | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using where |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
</code></pre>
  <p data-lake-id="ucd821c8f" id="ucd821c8f"><br></p>
  <p data-lake-id="u3194041f" id="u3194041f"><span data-lake-id="uad4bbf61" id="uad4bbf61">但是如果是如下形式的计算还是可以走索引的：</span></p>
  <p data-lake-id="u7129a8dd" id="u7129a8dd"><span data-lake-id="u89d27738" id="u89d27738">​</span><br></p>
  <pre lang="java"><code>
select * from mytable where age = 12 - 1;

+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+                                                                
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |                                                                
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+                                                                
|  1 | SIMPLE      | mytable | NULL       | ref  | age           | age  | 5       | const |    1 |   100.00 | NULL  |                                                                
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
</code></pre>
  <p data-lake-id="u7bf92a56" id="u7bf92a56"><br></p>
  <h3 data-lake-id="y0Xx9" id="y0Xx9"><span data-lake-id="ucb8280d9" id="ucb8280d9">对索引列进行函数操作</span></h3>
  <p data-lake-id="ua8e77c95" id="ua8e77c95"><br></p>
  <pre lang="java"><code>
SELECT * FROM mytable WHERE create_time = '2023-04-01 00:00:00';

+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+                                                         
| id | select_type | table   | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |                                                         
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+                                                         
|  1 | SIMPLE      | mytable | NULL       | ref  | create_time   | create_time | 6       | const |    1 |   100.00 | NULL  |                                                         
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+                                                         
</code></pre>
  <p data-lake-id="u89fd9d54" id="u89fd9d54"><br></p>
  <p data-lake-id="ue20df8c0" id="ue20df8c0"><span data-lake-id="u9c67df09" id="u9c67df09">以上SQL是可以走索引的，但是如果我们在字段中增加函数操作的话，就会索引失效：</span></p>
  <p data-lake-id="uaeb797cd" id="uaeb797cd"><br></p>
  <pre lang="java"><code>
SELECT * FROM mytable WHERE YEAR(create_time) = 2022;

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
|  1 | SIMPLE      | mytable | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using where |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
1 row in set, 1 warning (0.00 sec)
</code></pre>
  <p data-lake-id="u18f16ae7" id="u18f16ae7"><br></p>
  <p data-lake-id="u57514ae9" id="u57514ae9"><br></p>
  <h3 data-lake-id="Cc4kA" id="Cc4kA"><span data-lake-id="u8fd5cefd" id="u8fd5cefd">使用OR</span></h3>
  <p data-lake-id="u7b28e59f" id="u7b28e59f"><br></p>
  <pre lang="java"><code>
SELECT * FROM mytable WHERE name = 'Hollis' and age &gt; 18;

+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+                                                               
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra |                                                               
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+                                                               
|  1 | SIMPLE      | mytable | NULL       | const | name,age      | name | 202     | const |    1 |   100.00 | NULL  |                                                               
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+                                                               
</code></pre>
  <p data-lake-id="u68ff557e" id="u68ff557e"><br></p>
  <p data-lake-id="u3040861a" id="u3040861a"><span data-lake-id="u14d123d8" id="u14d123d8">但是如果使用OR的话，并且OR的两边存在&lt;或者&gt;的时候，就会索引失效，如：</span></p>
  <pre lang="java"><code>
SELECT * FROM mytable WHERE name = 'Hollis' OR age &gt; 18;

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
|  1 | SIMPLE      | mytable | NULL       | ALL  | name,age      | NULL | NULL    | NULL |   10 |    40.00 | Using where |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
</code></pre>
  <p data-lake-id="u07368c0c" id="u07368c0c"><br></p>
  <p data-lake-id="u3c3998bd" id="u3c3998bd"><span data-lake-id="u06edca94" id="u06edca94">但是如果OR两边都是=判断，并且两个字段都有索引，那么也是可以走索引的，如：</span></p>
  <p data-lake-id="ue24224fe" id="ue24224fe"><span data-lake-id="uc7d236df" id="uc7d236df">​</span><br></p>
  <pre lang="java"><code>
mysql&gt; explain SELECT * FROM mytable WHERE name = 'Hollis' OR age = 18;                                                                                                              
+----+-------------+---------+------------+-------------+---------------+----------+---------+------+------+----------+------------------------------------+                         
| id | select_type | table   | partitions | type        | possible_keys | key      | key_len | ref  | rows | filtered | Extra                              |                         
+----+-------------+---------+------------+-------------+---------------+----------+---------+------+------+----------+------------------------------------+                         
|  1 | SIMPLE      | mytable | NULL       | index_merge | name,age      | name,age | 202,5   | NULL |    2 |   100.00 | Using union(name,age); Using where |                         
+----+-------------+---------+------------+-------------+---------------+----------+---------+------+------+----------+------------------------------------+
</code></pre>
  <p data-lake-id="uaeb452fb" id="uaeb452fb"><br></p>
  <h3 data-lake-id="IfVVR" id="IfVVR"><span data-lake-id="ue97dba3a" id="ue97dba3a">like操作</span></h3>
  <p data-lake-id="uf3befffd" id="uf3befffd"><br></p>
  <pre lang="java"><code>
SELECT * FROM mytable WHERE name like '%Hollis%';                                                                                                                     
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
|  1 | SIMPLE      | mytable | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    11.11 | Using where |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           


                                                                                                                                                                                     
SELECT * FROM mytable WHERE name like '%Hollis';                                                                                                                      
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
|  1 | SIMPLE      | mytable | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    11.11 | Using where |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                                                                                                                                                                          


SELECT * FROM mytable WHERE name like 'Hollis%';                                                                                                                      
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
|  1 | SIMPLE      | mytable | NULL       | ALL  | name          | name | NULL    | NULL |   10 |   100.00 | Using where |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           


                                                                                                                                                                                     
SELECT * FROM mytable WHERE name like 'Holl%is';                                                                                                                      
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
|  1 | SIMPLE      | mytable | NULL       | ALL  | name          | name | NULL    | NULL |   10 |   100.00 | Using where |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           

</code></pre>
  <p data-lake-id="u2e1fd950" id="u2e1fd950"><br></p>
  <p data-lake-id="u39b37dda" id="u39b37dda"><span data-lake-id="u31b1e274" id="u31b1e274">以上四种like，"Hollis%"和"Holl%is"这两种可以走索引，但是如果是"%Hollis%"和"%Hollis"就没办法走索引了。</span></p>
  <p data-lake-id="u0d2ac67d" id="u0d2ac67d"><br></p>
  <h3 data-lake-id="WG314" id="WG314"><span data-lake-id="ub94a5135" id="ub94a5135">隐式类型转换</span></h3>
  <p data-lake-id="ue941389a" id="ue941389a"><br></p>
  <pre lang="java"><code>
select * from mytable where name = 1;                                                                                                                                 
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
|  1 | SIMPLE      | mytable | NULL       | ALL  | name          | NULL | NULL    | NULL |   10 |    10.00 | Using where |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
</code></pre>
  <p data-lake-id="ueec936f9" id="ueec936f9"><br></p>
  <p data-lake-id="ue2681ec7" id="ue2681ec7"><span data-lake-id="u72eea384" id="u72eea384">以上情况，name是一个varchar类型，但是我们用int类型查询，这种是会导致索引失效的。</span></p>
  <p data-lake-id="ued709c13" id="ued709c13"><br></p>
  <p data-lake-id="uafad5c8d" id="uafad5c8d"><span data-lake-id="u0e19bdb3" id="u0e19bdb3">这种情况有一个特例，如果字段类型为int类型，而查询条件添加了单引号或双引号，则Mysql会参数转化为int类型，这种情况也能走索引：</span></p>
  <p data-lake-id="u35ea258a" id="u35ea258a"><span data-lake-id="u9dae7873" id="u9dae7873" class="lake-fontsize-12" style="color: rgb(51, 51, 51)">​</span><br></p>
  <pre lang="java"><code>
select * from mytable where age = '1';

+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+                                                                
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |                                                                
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+                                                                
|  1 | SIMPLE      | mytable | NULL       | ref  | age           | age  | 5       | const |    1 |   100.00 | NULL  |                                                                
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+ 
</code></pre>
  <p data-lake-id="ue78c1842" id="ue78c1842"><br></p>
  <p data-lake-id="ud94da720" id="ud94da720"><br></p>
  <h3 data-lake-id="HVy3s" id="HVy3s"><span data-lake-id="u99718812" id="u99718812">不等于比较</span></h3>
  <p data-lake-id="u32d7d971" id="u32d7d971"><br></p>
  <pre lang="java"><code>
SELECT * FROM mytable WHERE age != 18;

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
|  1 | SIMPLE      | mytable | NULL       | ALL  | age           | NULL | NULL    | NULL |   10 |   100.00 | Using where |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
</code></pre>
  <p data-lake-id="uc93e7981" id="uc93e7981"><br></p>
  <p data-lake-id="u41ea3d12" id="u41ea3d12"><span data-lake-id="u6688c870" id="u6688c870" class="lake-fontsize-12">但是也不全是，比如以下的用ID进行!=比较的时候，是可能走索引的：</span></p>
  <p data-lake-id="u4e05aee6" id="u4e05aee6"><span data-lake-id="u257e01f3" id="u257e01f3" class="lake-fontsize-12">​</span><br></p>
  <pre lang="java"><code>
SELECT * FROM mytable WHERE id != 18;                                                                                                                                 
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+                                                       
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |                                                       
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+                                                       
|  1 | SIMPLE      | mytable | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   12 |   100.00 | Using where |                                                       
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 
</code></pre>
  <p data-lake-id="ua560be97" id="ua560be97"><br></p>
  <p data-lake-id="u84d7c398" id="u84d7c398"><span data-lake-id="ufc4ea7c4" id="ufc4ea7c4">!=这种情况，索引失效与索引的选择性、数据分布情况等因素有关，不能简单地说是由于查询条件中包含 != 这个操作符而导致索引失效。</span></p>
  <h3 data-lake-id="wNbJY" id="wNbJY"><span data-lake-id="ud835129f" id="ud835129f">is not null</span></h3>
  <p data-lake-id="u98919ffe" id="u98919ffe"><br></p>
  <p data-lake-id="ue439d565" id="ue439d565"><span data-lake-id="u47fef664" id="u47fef664" class="lake-fontsize-12">以下情况是索引失效的：</span></p>
  <p data-lake-id="u487d9640" id="u487d9640"><br></p>
  <pre lang="java"><code>
SELECT * FROM mytable WHERE name is not null

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
|  1 | SIMPLE      | mytable | NULL       | ALL  | name          | NULL | NULL    | NULL |   10 |    90.00 | Using where |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
</code></pre>
  <p data-lake-id="u9101e37f" id="u9101e37f"><br></p>
  <h3 data-lake-id="S1QlX" id="S1QlX"><span data-lake-id="ue3454639" id="ue3454639" style="color: rgb(51, 51, 51)">order by</span></h3>
  <p data-lake-id="ub174a9af" id="ub174a9af"><br></p>
  <pre lang="java"><code>
SELECT * FROM mytable order by age

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+                                                        
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |                                                        
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+                                                        
|  1 | SIMPLE      | mytable | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using filesort |                                                        
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+                                                        
</code></pre>
  <p data-lake-id="uafe9b268" id="uafe9b268"><br></p>
  <p data-lake-id="u0ee55dbf" id="u0ee55dbf"><br></p>
  <h3 data-lake-id="fI7hq" id="fI7hq"><span data-lake-id="u84b917c3" id="u84b917c3">in</span></h3>
  <p data-lake-id="ufbfff0b0" id="ufbfff0b0"><br></p>
  <p data-lake-id="ub44b07bc" id="ub44b07bc"><span data-lake-id="u7ba632af" id="u7ba632af" class="lake-fontsize-12">使用in的时候，有可能走索引，也有可能不走，一般在in中的值比较少的时候可能会走索引优化，但是如果选项比较多的时候，可能会不走索引：</span></p>
  <p data-lake-id="u77a8c832" id="u77a8c832"><span data-lake-id="ucb6a15e1" id="ucb6a15e1" class="lake-fontsize-12">​</span><br></p>
  <pre lang="java"><code>
mysql&gt; explain select * from mytable where name in ("Hollis");                                                                                                                       
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+                                                               
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra |                                                               
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+                                                               
|  1 | SIMPLE      | mytable | NULL       | const | name          | name | 202     | const |    1 |   100.00 | NULL  |                                                               
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+                                                                                                                                                                                                            
                                                                                                                                                                                     
mysql&gt; explain select * from mytable where name in ("Hollis","hsh");                                                                                                                 
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+                                                
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |                                                
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+                                                
|  1 | SIMPLE      | mytable | NULL       | range | name          | name | 202     | NULL |    2 |   100.00 | Using index condition |                                                
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+                                                

                                                             
mysql&gt; explain select * from mytable where name in ("Hollis","hsh","s");                                                                                                             
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+                                                           
|  1 | SIMPLE      | mytable | NULL       | ALL  | name          | NULL | NULL    | NULL |   11 |    27.27 | Using where |                                                           
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
</code></pre>
 </body>
</html>